[mysql]
auto-rehash

[mysqld]
##: User Configuration
####: Global Configuration Defaults
user                                    = {{ mysql_user }}                          # mysql
basedir                                 = {{ mysql_base_dir }}                      # /usr/local/mysql/
datadir                                 = {{ mysql_data_dir_base }}/data/{{ mysql_port }}    # /usr/local/mysql/data
tmpdir                                  = {{ mysql_data_dir_base }}/tmp/{{ mysql_port }}
log_bin                                 = {{ mysql_data_dir_base }}/log/binlog/{{ mysql_port }}/mysql-bin          # ON
relay_log                               = {{ mysql_data_dir_base }}/log/relaylog/{{ mysql_port }}/relay-bin
innodb_log_group_home_dir               = {{ mysql_data_dir_base }}/log/redolog/{{ mysql_port }}       # datadir
port                                    = {{ mysql_port }}                          # 3306
admin_port                              = {{ (mysql_port*10 + 2) | string }}  # 33062
mysqlx_port                             = {{ (mysql_port*10) | string }}       # 33060
{% if sock_type == 1 %}
socket                                  = mysql.sock                               # /tmp/mysql.sock          
mysqlx_socket                           = mysqlx.sock                              # /tmp/mysqlx.sock
{% elif sock_type == 2 %}
socket                                  = /tmp/mysql{{ mysql_port }}.sock
mysqlx_socket                           = /tmp/mysqlx{{ mysql_port }}.sock
{% elif sock_type == 3 %}
socket                                  = {{ mysql_data_dir_base }}/run/mysql{{ mysql_port }}.sock
mysqlx_socket                           = {{ mysql_data_dir_base }}/run/mysqlx{{ mysql_port }}.sock
{% endif %}

####: Customizable User Settings
character_set_server                    = {{ mysql_character_set_server }}          # utf8mb4
max_connections                         = {{ mysql_max_connections | default(1000) }}  # 151
transaction_isolation                   = {{ mysql_transaction_isolation }}        # REPEATABLE-READ
innodb_open_files                       = {{ mysql_innodb_open_files }}         # -1 (auto)
innodb_log_buffer_size                  = {{ mysql_innodb_log_buffer_size }}   # 67108864(64M)
default_time_zone                       = {{ mysql_default_time_zone }}

##: Automatic Settings 
####: Auto-Generated by dbops
server_id                               = {{ mysql_server_id }}                     # 0
report_host                             = {{ ansible_default_ipv4.address }}

####: Special Calculations by dbops
thread_cache_size                       = {{ [(server_specs_processor_count | int ) * 8, 256 ] | max }}    # 8 + (max_connections / 100), capped to a limit of 100
table_open_cache_instances              = {{ [(server_specs_processor_count | int ) // 2, 16 ] | max }}   # 16
replica_parallel_workers                = {{ [(server_specs_processor_count | int ) * 4 ,32] | min }}    # 0

####: Dedicated Server Settings or Auto-Generated by dbops Specifications
{% if server_specs != "auto" %}
innodb_read_io_threads={{ [(server_specs_processor_count | int) // 2, 4] | max }}  # (available logical processors / 2), min of 4
innodb_purge_threads={{ 1 if server_specs_processor_count | int <= 16 else 4 }}  # 1 if # of available logical processors is <= 16; otherwise 4
innodb_parallel_read_threads={{ [(server_specs_processor_count | int) // 8, 4] | max }}  # Default value: calculated by the number of available logical processors on the system divided by 8, with a minimum default value of 4
{# 根据服务器规格设置innodb_buffer_pool_size和innodb_buffer_pool_instances #}
{% set temp_ram = server_specs_memtotal_gb | int * 0.03 %}
{% set temptable_max_ram_val = [temp_ram, 1] %}
{% if temptable_max_ram_val | max > 4 %}
{% set temptable_max_ram_final = 4 %}
{% else %}
{% set temptable_max_ram_final = temptable_max_ram_val | max %}
{% endif %}
temptable_max_ram={{ (temptable_max_ram_final | round(0, 'ceil')) | int }}GB  # 3% of total memory: min 1 GB, max 4 GB
{% set innodb_buffer_pool_size_mb = ((server_specs_memtotal_gb | int) * 0.6 * 1024) | round(0, 'ceil') | int %}
innodb_buffer_pool_size={{ innodb_buffer_pool_size_mb }}M  # 134217728(128M)  # dbops set it 60% of total memory
{% if innodb_buffer_pool_size_mb <= 1024 %}
innodb_buffer_pool_instances=1  # 1 if innodb_buffer_pool_size <= 1 GiB; otherwise min(1/2*(innodb_buffer_pool_size/innodb_buffer_pool_chunk_size), 1/4*CPU cores), range 1-64.
{% else %}
{% set buffer_pool_chunk_size_mb = 128 %}
{% set buffer_pool_hint = (innodb_buffer_pool_size_mb / buffer_pool_chunk_size_mb / 2) | round(0, 'ceil') | int %}
{% set cpu_hint = server_specs_processor_count | int // 4 %}
{% set instances = [buffer_pool_hint, cpu_hint, 64] | min %}
innodb_buffer_pool_instances={{ instances }} # 1 if innodb_buffer_pool_size <= 1 GiB; otherwise min(1/2*(innodb_buffer_pool_size/innodb_buffer_pool_chunk_size), 1/4*CPU cores), range 1-64.
{% endif %}
{% set redo_log_capacity_gb = ((server_specs_processor_count | int) / 2) | round(0, 'ceil') | int %}
{% if redo_log_capacity_gb > 16 %}
{% set redo_log_capacity_gb = 16 %}
{% endif %}
innodb_redo_log_capacity={{ redo_log_capacity_gb }}GB  # Setup by dbops, refer to the implementation of innodb_dedicated_server. Redo log capacity: (logical processors / 2) GB, max 16 GB.
{% endif %}
{% if server_specs == "auto" %}
innodb_dedicated_server=ON  # OFF , ON will auto set innodb_buffer_pool_size and innodb_redo_log_capacity
{% else %}
innodb_dedicated_server=OFF  # OFF
{% endif %}

##: Standard Configurations
log_bin_trust_function_creators          = ON                                        # OFF
max_prepared_stmt_count                  = 1048576                                   # 16382
log_timestamps                           = SYSTEM                                    # UTC
pid_file                                 = mysqld.pid
read_only                                = OFF                                       # OFF
super_read_only                          = OFF                                       # OFF
skip_name_resolve                        = ON                                        # OFF
auto_increment_increment                 = 1                                         # 1
auto_increment_offset                    = 1                                         # 1
lower_case_table_names                   = 1                                         # 0
open_files_limit                         = 65536                                     # 5000, with possible adjustment									    									    
table_open_cache                         = 4000                                      # 4000
table_definition_cache                   = 2000                                      # MIN(400 + table_open_cache / 2, 2000)									    
local_infile                             = OFF                                       # OFF
disabled_storage_engines                 = archive,blackhole,example,federated,memory,merge,ndb,myisam  # empty
plugin_load                              = "rpl_semi_sync_source=semisync_source.so;rpl_semi_sync_replica=semisync_replica.so"
admin_address                            = 127.0.0.1                                 # null
event_scheduler                          = OFF                                       # ON
sql_require_primary_key                  = ON                                        # OFF
activate_all_roles_on_login              = ON                                        # OFF
plugin-load-add = mysql_clone.so        
#default_authentication_plugin           = caching_sha2_password                     # caching_sha2_password
explicit_defaults_for_timestamp          = ON                                        # ON
log_error_verbosity                      = 2                                         # 2
max_allowed_packet                       = 64M                                       # 64M
terminology_use_previous                 = NONE                                      # NONE
autocommit                               = ON                                        # ON
sql_generate_invisible_primary_key       = ON                                        # OFF

####: for binlog
binlog_expire_logs_seconds              = 604800                                    # 2592000 (equivalent to 30 days) 
binlog_error_action                     = ABORT_SERVER                              # ABORT_SERVER
binlog_rows_query_log_events            = ON                                        # OFF
log_replica_updates                     = ON                                        # ON
binlog_checksum                         = CRC32                                     # CRC32
sync_binlog                             = 1                                         # 1
replica_preserve_commit_order           = ON                                        # ON

####: for gtid
gtid_executed_compression_period        = 0                                         # 0
gtid_mode                               = ON                                        # OFF
enforce_gtid_consistency                = ON                                        # OFF

####: for replication
skip_replica_start                      = OFF                                       # OFF
relay_log_recovery                      = ON                                        # OFF
#replica_parallel_type                   = LOGICAL_CLOCK                             # LOGICAL_CLOCK, is deprecated and will be removed in a future release

loose-rpl_semi_sync_source_enabled      = ON                                        # OFF
loose-rpl_semi_sync_replica_enabled     = ON                                        # OFF
loose-rpl_semi_sync_source_timeout      = 1000                                      # 10000(10 second)
binlog_group_commit_sync_delay          = 500                                       # 0 500(0.05% second) 
binlog_group_commit_sync_no_delay_count = 13                                        # 0

####: for error-log
log_error                               = error.log                                 # hostname.err

general_log                             = OFF                                       # OFF
general_log_file                        = general.log                               # hostname.log

####: for slow query log
slow_query_log                          = ON                                        # OFF
slow_query_log_file                     = slow.log                                  # hostname.log
log_queries_not_using_indexes           = OFF                                       # OFF
long_query_time                         = 2.000000                                  # 10.000000



####: for innodb
default_storage_engine                          = InnoDB                               # InnoDB
default_tmp_storage_engine                      = InnoDB                               # InnoDB
innodb_data_file_path                           = ibdata1:64M:autoextend               # ibdata1:12M:autoextend
innodb_temp_data_file_path                      = ibtmp1:12M:autoextend                # ibtmp1:12M:autoextend
innodb_buffer_pool_filename                     = ib_buffer_pool                       # ib_buffer_pool
innodb_file_per_table                           = ON                                    # ON
innodb_online_alter_log_max_size                = 128M                                  # 134217728(128M)
innodb_page_size                                = 16K                                   # 16384(16K)
innodb_thread_concurrency                       = 0                                     # 0
innodb_write_io_threads                         = 4                                     # 4
innodb_print_all_deadlocks                      = ON                                    # OFF
innodb_deadlock_detect                          = ON                                    # ON
innodb_lock_wait_timeout                        = 50                                    # 50
innodb_spin_wait_delay                          = 6                                     # 6
innodb_autoinc_lock_mode                        = 2                                     # 1
innodb_io_capacity                              = 10000                                 # 10000
innodb_io_capacity_max                          = 20000                                 # 2 * innodb_io_capacity
innodb_adaptive_hash_index                      = OFF                                   # OFF
innodb_change_buffering                         = none                                  # none
innodb_flush_neighbors                          = 0                                     # 0
innodb_doublewrite                              = ON                                    # ON
innodb_flush_log_at_timeout                     = 1                                     # 1
innodb_flush_log_at_trx_commit                  = 1                                     # 1
#innodb_flush_method                             = O_DIRECT                              # fsync
#innodb_change_buffer_max_size                   = 25                                    # 25

innodb_old_blocks_pct                           = 37                                    # 37
innodb_old_blocks_time                          = 1000                                  # 1000

innodb_read_ahead_threshold                     = 56                                    # 56 (0..64)
innodb_random_read_ahead                        = OFF                                   # OFF

innodb_buffer_pool_dump_pct                     = 25                                    # 25 
innodb_buffer_pool_dump_at_shutdown             = ON                                    # ON
innodb_buffer_pool_load_at_startup              = ON                                    # ON

innodb_undo_log_truncate                        = ON                                    # ON
innodb_max_undo_log_size                        = 2G                                    # 1G

innodb_stats_auto_recalc                        = ON                                    # ON
innodb_stats_persistent                         = ON                                    # ON
innodb_stats_persistent_sample_pages            = 20                                    # 20

####: for performance_schema
performance_schema                                           = ON                                    # ON
performance_schema_show_processlist                          = ON                                    # OFF

performance_schema_consumer_events_stages_current            = ON                                    # OFF
performance_schema_consumer_events_stages_history            = ON                                    # OFF
performance_schema_consumer_events_stages_history_long       = OFF                                   # OFF
performance_schema_consumer_events_statements_cpu            = OFF                                   # OFF
performance_schema_consumer_events_statements_current        = ON                                    # ON
performance_schema_consumer_events_statements_history        = ON                                    # ON
performance_schema_consumer_events_statements_history_long   = OFF                                   # OFF
performance_schema_consumer_events_transactions_current      = ON                                    # ON
performance_schema_consumer_events_transactions_history      = ON                                    # ON
performance_schema_consumer_events_transactions_history_long = OFF                                   # OFF
performance_schema_consumer_events_waits_current             = ON                                    # OFF
performance_schema_consumer_events_waits_history             = ON                                    # OFF
performance_schema_consumer_events_waits_history_long        = OFF                                   # OFF
performance_schema_consumer_global_instrumentation           = ON                                    # ON
performance_schema_consumer_thread_instrumentation           = ON                                    # ON
performance_schema_consumer_statements_digest                = ON                                    # ON

performance_schema_instrument                                = 'memory/%=COUNTED'

{% if mtls_with_mysql_group_replication == 1 %}
####: for mysql group replication 
loose-group_replication_recovery_retry_count          = 10                                     # 10
loose-group_replication_recovery_reconnect_interval   = 60                                     # 60
loose-group_replication_ip_allowlist                  = AUTOMATIC                              # AUTOMATIC
loose-group_replication_group_name = "{{ mysql_group_replication_group_name if mgr_use_random_uuid == 1 else 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa' }}"
loose-group_replication_start_on_boot                 = OFF                                    # ON
loose-group_replication_bootstrap_group               = OFF                                    # OFF
loose-group_replication_single_primary_mode           = ON                                     # ON
loose-group_replication_enforce_update_everywhere_checks = OFF                                  # OFF
loose-group_replication_gtid_assignment_block_size    = 1000000                                # 1000000
loose-group_replication_poll_spin_loops               = 0                                      # 0
loose-group_replication_compression_threshold         = 1000000                                # 1000000
loose-group_replication_flow_control_mode             = QUOTA                                  # QUOTA
loose-group_replication_consistency                   = BEFORE_ON_PRIMARY_FAILOVER             # BEFORE_ON_PRIMARY_FAILOVER
loose-group_replication_exit_state_action             = OFFLINE_MODE                           # OFFLINE_MODE
loose-group_replication_paxos_single_leader           = ON                                     # OFF
loose-group_replication_recovery_get_public_key       = ON                                     # OFF
{% for ip in ansible_all_ipv4_addresses %}
    {%- for host_ip in mysql_mgr_hosts %}
        {%- if ip == host_ip %}
loose-group_replication_local_address                 = "{{ ip }}:{{ mysql_mgr_port }}"
        {%- endif %}
    {%- endfor %}
{% endfor %}

{% set gs = ':' + (mysql_mgr_port | string)+',' %}
loose-group_replication_group_seeds                   = "{{ mysql_mgr_hosts | join(gs) + ':' + (mysql_mgr_port | string) }}"
{% endif %}
